// 引入express模块
const express = require('express')
// 引入连接模块
const connection = require('../connection')
// 创建路由器对象
const router = express.Router()
// 添加路由
// 1.搜索员工(get /search)
router.get('/search', (req, res) => {
    // 获取get传递的参数
    var obj = req.query;
    console.log(obj)
    // 执行SQL命令
    connection.query('select * from emp where ename=?', [obj.ename], (err, r) => {
        if (err) {
            throw err
        }
        if (r.length === 0) {
            res.send({
                code: 400,
                msg: '员工不存在'
            })
        } else {
            res.send({
                code: 200,
                msg: '查询成功',
                data: r
            })
        }
    })
});
router.get('/searchs', (req, res) => {
    // 获取get传递的参数
    var obj = req.query;
    console.log(obj)
    // 执行SQL命令
    connection.query('select * from emp', [], (err, r) => {
        if (err) {
            throw err
        }
        res.send({
            code: 200,
            msg: '查询成功',
            data: r
        })
    })
});
router.post('/add', (req, res, next) => {
    let obj = req.body;
    console.log(obj);
    connection.query('insert into emp set ?', [obj], (err, r) => {
        if (err) {
            //如果sql命令出现错误，往后执行下一个错误处理中间件
            return next(err);
        }
        res.send({
            code: 200,
            msg: '员工添加成功',
            data: r
        })
    })
});
router.delete('/del', (req, res) => {
    // res.setHeader('Content-Type', 'application/json;charset=utf-8');
    // 获取get传递的参数
    var obj = req.query;
    console.log(obj)
    // 执行SQL命令
    connection.query('delete from emp where eid = ?', [obj.eid], (err, r) => {
        if (err) {
            throw err
        }
        if (r.affectedRows === 0) {
            res.send({
                code: 400,
                msg: '找不到此员工'
            })
        } else {
            res.send({
                code: 200,
                msg: '删除成功',
                data: r
            })
        }
    })

});
router.put('/upd', (req, res, next) => {
    // res.setHeader('Content-Type', 'application/json;charset=utf-8');
    // 获取get传递的参数
    var obj = req.body;
    console.log(obj)
    // 执行SQL命令
    connection.query('update emp set ? where eid=?', [obj, obj.eid], (err, r) => {
        if (err) {
            return next(err);
        }
        if (r.affectedRows === 0) {
            res.send({
                code: 400,
                msg: '找不到此编号的员工，修改失败'
            })
        } else {
            res.send({
                code: 200,
                msg: '修改成功'
            })
        }
    })

});

//分页查询
router.get('/list', (req, res, next) => {
    let obj = req.query;
    if (!obj.pno) {
        obj.pno=1;
    };
    if (!obj.count) {
        obj.count=5;
    };
    let start = (obj.pno - 1) * obj.count;
    let size = parseInt(obj.count);
    let sql1 = 'select eid,ename,sex,birthday,salary,dname from emp,dept where deptid=did limit ?,?';
    let sql2 = 'select count(*) as count from emp';
    connection.query(`${sql1};${sql2}`, [start, size], (err, r) => {
        if (err) {
            return next(err);
        }
        console.log(r);
        res.send({
            code: 200,
            count: r[1][0].count,
            index: obj.pno,
            msg: r[0]
        })
    })
})
// 暴露路由器对象
module.exports = router